﻿Imports System.Data.OleDb
Public Class IMPORTEXCEL
    Private xl As IMPORTCLASS
    Private fileLocation As String = ""
    Private fileName As String = ""
    Private excel As New EXCELCLASS()
    Private tbname As String = ""

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

    End Sub

    Public Sub New(tablename As String)
        ' This call is required by the designer.
        InitializeComponent()
        tbname = tablename
        ' Add any initialization after the InitializeComponent() call.
    End Sub

    Private Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
        'xl.Insert(dgvData)
        If tbname = "xrayitem" Then
            xl.InsertXrayItem(dgvData)
        ElseIf tbname = "operatitem" Then
            xl.InsertOperatitem(dgvData)
        ElseIf tbname = "lab_order" Then
            xl.InsertLab_order(dgvData)
        ElseIf tbname = "Drugitem" Then
            xl.InsertDrugitem(dgvData)
        ElseIf tbname = "medsupplyitem" Then
            xl.Insertmedsupplyitem(dgvData)
        Else
            xl.Insert(dgvData)
        End If
    End Sub

    Private Sub btnOpenUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        xl.UPDATE(dgvData)
    End Sub

    Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click
        If tbname.Length > 0 Then
            Dim openFileDialog1 As New OpenFileDialog()
            openFileDialog1.InitialDirectory = "D:\"
            openFileDialog1.Filter = "Excel File 2003 (*.xls)|*.xls|Excel File 2007-2013 (*.xlsx)|*.xlsx"
            openFileDialog1.FilterIndex = 2
            openFileDialog1.RestoreDirectory = True
            If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                Me.fileLocation = openFileDialog1.FileName
                excel.OpenFile(fileLocation)
                If (excel.GetSheet.Name IsNot Nothing) Then
                    dgvData.DataSource = xl.ExcelOpen(fileLocation, excel.GetSheet.Name)
                    'fillNumRow()
                End If
            End If
        Else
            MessageBox.Show("กรุณาเลือกตาราง", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

    End Sub

    Private Sub fillNumRow()
        For i As Integer = 1 To dgvData.Rows.Count
            dgvData.Rows(i - 1).HeaderCell.Value = i.ToString
        Next i

    End Sub



    Private Sub btnTemplate_Click(sender As Object, e As EventArgs) Handles btnTemplate.Click
        If tbname = "" Then
            MsgBox("กรุณาเลือก Table")
            Return
        End If
        Dim saveFileDialog1 As New SaveFileDialog()
        saveFileDialog1.Filter = "Excel File 2003 (*.xls)|*.xls|Excel File 2007-2013 (*.xlsx)|*.xlsx"
        saveFileDialog1.FilterIndex = 2
        saveFileDialog1.RestoreDirectory = True
        saveFileDialog1.FileName = tbname & "_MASTER.xlsx"
        If saveFileDialog1.ShowDialog() = DialogResult.OK Then
            'MsgBox(saveFileDialog1.FileName.ToString)
            excel.NewFile(saveFileDialog1.FileName, tbname)
        End If
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        excel.CloseFile()
    End Sub

    Private Sub btnExportData_Click(sender As Object, e As EventArgs) Handles btnExportData.Click
        If tbname = "" Then
            MsgBox("กรุณาเลือก Table")
            Return
        End If
        Dim saveFileDialog1 As New SaveFileDialog()
        saveFileDialog1.Filter = "Excel File 2003 (*.xls)|*.xls|Excel File 2007-2013 (*.xlsx)|*.xlsx"
        saveFileDialog1.FilterIndex = 2
        saveFileDialog1.RestoreDirectory = True
        saveFileDialog1.FileName = tbname & "_MASTER.xlsx"
        If saveFileDialog1.ShowDialog() = DialogResult.OK Then
            'MsgBox(saveFileDialog1.FileName.ToString)
            excel.ExportData(saveFileDialog1.FileName, tbname)
        End If
    End Sub

    
   
    Private Sub IMPORTEXCEL_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If xl IsNot Nothing Then
            xl = Nothing
        End If
        xl = New IMPORTCLASS(tbname)
    End Sub
End Class
